package com.sf.osms.dep.dao.impl;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Restrictions;
import org.springframework.stereotype.Repository;

import com.sf.novatar.base.dao.BaseBeanDao;
import com.sf.novatar.base.domain.RequestParams;
import com.sf.osms.dep.dao.IRouterDao;
import com.sf.osms.dep.domain.Router;

@Repository
public class IRouterDaoImpl extends BaseBeanDao<Long,Router> implements IRouterDao{
	
	@Override
	public List<Router> findRouterInfo(String sfWayfill, Integer eventCode,
			boolean failed) {
		String sql = null;
		if(null != eventCode){
			sql = "select * from wbs_tt_router where SF_WAYBILL = ? and EVENT_CODE = ?";
		}else{
			sql = "select * from wbs_tt_router where SF_WAYBILL = ? and EVENT_CODE = null";
		}
		
		if(failed) {
			sql += " and STATUS = 1";
		} else {
			sql += " and STATUS <> 1";
		}
		Session session = this.getCurrentSession();
		SQLQuery query = session.createSQLQuery(sql);
		query.setString(0, sfWayfill);
		if(null != eventCode){
			query.setInteger(1,eventCode);
		}
		
		@SuppressWarnings("unchecked")
		List<Router> list = query.addEntity(Router.class).list();
		return list;
	}


	@Override
	public List<Router> queryRoutersById(Long ... id) {
		
		DetachedCriteria criteria = DetachedCriteria.forClass(Router.class);
		criteria.add(Restrictions.in("id", id));
		
		return this.findBy(criteria);
		
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Router> queryRouterByStatus(int status) {
		String sql = "select * from wbs_tt_router where status = ? limit 1000";
		SQLQuery query=this.getCurrentSession().createSQLQuery(sql);
		query.setInteger(0, status);		
		return query.addEntity(Router.class).list();
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<Router> queryRouter(Router router){
		String sql = "select * from wbs_tt_router a where a.sf_waybill=? and event_code = ?";
		SQLQuery query=this.getCurrentSession().createSQLQuery(sql);
		query.setString(0, router.getSfWaybill());
		query.setInteger(1, router.getEventCode());
		return query.addEntity(Router.class).list();
	}

	@Override
	public List<Router> queryRouterNeedPod(String country) {
		String sql = 
				"SELECT "+ 
				"r.id,r.agent_id,r.country,r.sf_waybill,r.agent_code,r.agent_waybill,r.net_num,r.operator_id,r.event_code,r.reason_code, "+
				"r.bar_date,r.bar_time,r.bar_upload_type_code,r.courier_code,r.encrypt_string,r.bar_sn,r.batch_code,r.op_name, "+
				"r.op_attach_info,r.contnr_code,IF (r.phone_zone IS NULL || LENGTH(trim(r.phone_zone)) = 0,g.pod_sign,r.phone_zone) phone_zone,r.phone,r.pay_flg, "+
				"r.subbill_piece_qty,r.weight_qty,r.fee_amt,r.other_info,r.schedule_code,r.accountant_code,r.sign_type_code, "+
				"r.stop_over_flg,r.dest_zone_code,r.src_contnr_code,r.truck_plate_code,r.extend_attach_1,r.extend_attach_2, "+
				"r.extend_attach_3,r.extend_attach_4,r.extend_attach_5,r.file_name,r.insert_time,r.status,r.status_dt,r.send_count, "+
				"r.fail_reason,r.pod_status,r.pod_send_count,r.pod_bsp_return_info "+
				"FROM wbs_tt_router r,wbs_tt_agent g WHERE r.country = ? AND r.event_code = 80 AND r.status = 0 "+
				"AND r.sign_type_code IS NOT NULL AND r.agent_id = g.id "+
				"AND (r.pod_status = 0 OR (r.pod_status = 1 AND TO_DAYS(NOW()) - TO_DAYS(r.status_dt) <= 3)) LIMIT 500";
		SQLQuery query=this.getCurrentSession().createSQLQuery(sql);
		query.setString(0, country.trim());
		return query.addEntity(Router.class).list();
	}

	@Override
	public void updateRouterPodStatus(String sfWaybill, Integer statusCode,String failReason) {
		
		String sql = "update wbs_tt_router set pod_status = ? WHERE SF_WAYBILL = ? and EVENT_CODE=80 and pod_status=1 and TO_DAYS(NOW()) - TO_DAYS(status_dt) <= 3";
		if(StringUtils.isNotBlank(failReason)){
			sql = "update wbs_tt_router set pod_status = ? , pod_bsp_return_info = ? WHERE SF_WAYBILL = ? and EVENT_CODE=80 and pod_status=1 and TO_DAYS(NOW()) - TO_DAYS(status_dt) <= 3";
		}
		
		SQLQuery query=this.getCurrentSession().createSQLQuery(sql);
		if(StringUtils.isNotBlank(failReason)){
			query.setInteger(0, statusCode);
			query.setString(1, failReason);
			query.setString(2, sfWaybill);
		}else{
			query.setInteger(0, statusCode);
			query.setString(1, sfWaybill);
		}
		
		query.executeUpdate();
		
	}
	
	
	public int queryExportExcelCount(RequestParams params){
		StringBuilder sql = new StringBuilder();
		sql.append("select count(1) from wbs_tt_router r where 1=1 ");
		
		List<String> param = new ArrayList<String>();
		String startTime = params.getQueryValue("startTime");
		String endTime = params.getQueryValue("endTime");
		String countryCode = params.getQueryValue("countryCode");
		String agentCode = params.getQueryValue("agentCode");
		String mailNo = params.getQueryValue("mailNo");
		String eventCode = params.getQueryValue("eventCode");
		String status = params.getQueryValue("status");
		
		if(StringUtils.isNotEmpty(startTime)){
			sql.append("and r.INSERT_TIME >=? ");
			param.add(startTime);
		}
		if(StringUtils.isNotEmpty(endTime)){
			sql.append("and r.INSERT_TIME <=? ");
			param.add(endTime);
		}
		if(StringUtils.isNotEmpty(countryCode)){
			sql.append("and r.COUNTRY =? ");
			param.add(countryCode);
		}
		if(StringUtils.isNotEmpty(agentCode)){
			sql.append("and r.AGENT_CODE =? ");
			param.add(agentCode);
		}
		if(StringUtils.isNotEmpty(mailNo)){
			sql.append("and r.SF_WAYBILL =? ");
			param.add(mailNo);
		}
		if(StringUtils.isNotEmpty(eventCode)){
			sql.append("and r.EVENT_CODE =? ");
			param.add(eventCode);
		}
		if(StringUtils.isNotEmpty(status)){
			sql.append("and r.STATUS =? ");
			param.add(status);
		}
		
		SQLQuery countQuery = this.getCurrentSession().createSQLQuery(sql.toString());
		for (int i = 0; i < param.size(); i++) {
			countQuery.setParameter(i, param.get(i));
		}
		BigInteger count = (BigInteger)countQuery.uniqueResult();
		
		return count.intValue();
	}
	
	
	

	@Override
	public void updateRouterBySfWaybill(String sfwaybill,Integer eventCode,Integer status) {
		String sql = "update wbs_tt_router set status = ? where sf_waybill=? and event_code=?";
		SQLQuery query=this.getCurrentSession().createSQLQuery(sql);
		query.setString(1, sfwaybill);
		query.setInteger(0,status);
		query.setInteger(2, eventCode);
		query.executeUpdate();
	}


}
